import pandas as pd
from datetime import datetime, timedelta
from openpyxl.utils import get_column_letter

def __init_format(df, ws):
    col_list = df.columns.to_list()
    for i in range(len(col_list)):
        ws.column_dimensions[get_column_letter(i + 1)].width = 30

output_filename = '收益凭证导出_{}.xlsx'.format(datetime.now().strftime("%Y-%m-%d"))
writer = pd.ExcelWriter(output_filename, engine='openpyxl')  # , engine='xlsxwriter'
start_row_num = 3
blank_row_num = start_row_num - 1

vanilla_trades = []
vanilla_trade = {}
vanilla_trade['结构类型1111111111111111111111111111111111111'] = '123'
vanilla_trade['买卖方向'] = '卖'
vanilla_trades.append(vanilla_trade)

vanilla_trade1 = {}
vanilla_trade1['结构类型1111111111111111111111111111111111111'] = '123'
vanilla_trade1['买卖方向'] = '卖'
vanilla_trades.append(vanilla_trade1)

vanilla_sheet_name = '香草及其组合123'
vanilla_df = pd.DataFrame(vanilla_trades)
vanilla_df.to_excel(writer, sheet_name=vanilla_sheet_name, index=False)
workbook = writer.book
vanilla_sheet = workbook[vanilla_sheet_name]
vanilla_sheet.insert_rows(blank_row_num)
vanilla_sheet['A'+str(blank_row_num)] = '-'
__init_format(vanilla_df, vanilla_sheet)
writer.save()



